Just as computers have become more and more prevalent in everyday life, networks of linked computers have become important in distributing information amongst computer users. Many computer systems are organized according to a client/server metaphor. Generally, in client/server computing, end users are each provided with a desktop computer or terminal known as a “client.” The clients are connected using a network to another computer known as a “server”, because its general function is to serve or fulfill requests submitted by clients. Application programs running on the clients prepare requests and transmit them to the server over the network. A ‘network’ of computers can be any number of computers that are able to exchange information with one another. The computers may be arranged in any configuration and may be located in the same room or in different countries, so long as there is some way to connect them together (for example, by telephone lines or other communication systems) so they can exchange information. Just as computers may be connected together to make up a network, networks may also be connected together through tools known as bridges and gateways. These tools allow a computer in one network to exchange information with a computer in another network.
Of particular interest in today's computing environment are relational database applications. Relational DataBase Management System (RDBMS) software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Organization (ANSI) and the International Standards Organization (ISO).
In RDBMS software, all data is externally structured into tables. The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages such as C, COBOL, etc. Operators are provided in SQL that allow the user to manipulate the data, wherein each operator operates on either one or two tables and produces a new table as a result. The power of SQL lies in its ability to link information from multiple tables or views together to perform complex sets of procedures with a single statement.
One particular type of operator used in SQL is a SELECT SQL statement. A SELECT SQL statement contains many options that describe a statement's updatability, isolation, and certain special optimization requests. These options are called SELECT attributes. In a 3-tier client/server architecture (application program-driver-server), an application driver program, such as an OBDC (open database connectivity) or JAVA database connectivity driver, typically issues SQLs to the database server on behalf of the C or JAVA applications in the client. Such a driver would declare several types of cursors and dynamically prepare the user's SELECT SQL using a cursor as requested with the appropriate attributes. A cursor refers to a named control structure used by an application program to point to a specific row within some ordered set of rows. The cursor is used to retrieve rows from a set.
There are many different cursor attributes. Currently, a cursor attribute (such as its scrollability, holdability, sensitivity, returnability) can be specified at the time a cursor is declared in an embedded-SQL application. By way of example, typically all cursor attributes are hard-coded either as part of the DECLARE CURSOR SQL statement or as part of a SELECT statement itself, where a SELECT statement refers to a form of query, which can be embedded in an application program. When an application would like to dynamically prepare another SELECT statement using a cursor, it uses the PREPARE SQL statement. As is well understood in the art, the PREPARE statement is used by application programs to dynamically prepare an SQL statement for execution. The PREPARE statement creates an executable SQL statement, called a prepared statement, from a character string form of the statement, called a statement string. A typical dynamic preparation process of a SELECT statement using a declared cursor is shown in FIG. 1. The process includes a step to locate the appropriate cursor in a program (step 100). The SELECT statement is then parsed and a parse tree is built to represent the SELECT statement and all the specified cursor and SELECT attributes (i.e., these are attributes specified at static declare cursor time or as part of the SELECT statement) (step 102). The catalog statistics are then looked up to determine a best access path including modification of the built parse tree (step 104). The executable form of the SELECT statement then is built (step 106).
The set of cursors declared in the driver program is finite. As more cursor and SELECT attributes are created and available on a database server, the driver program must increase the number of cursors statically declared to be able to handle a great accommodate a specific user's need. A real problem for the driver program is the permutations of all these cursor attributes. To fully support every conceivable SQL application, the driver program has to declare cursors that have all the possible permutations of SELECT cursor attributes. As the number of cursor attributes increases, the total number of cursors climbs dramatically, thus increasing storage requirements.
While allowing all cursors to be specified as part of the SELECT statement is an option, this option clutters up the SELECT statement. A further option is to define a new SQL statement to accommodate attribute changes. However, defining a new statement introduces significant overhead for processing and concerns about the lifespan of the attributes with other considerations for thread use.
Accordingly, what is needed is an approach to defining cursor attributes that provides flexibility and avoids the overhead and inefficiency of introducing a new statement. The present invention addresses such a need.